/**
 * Užduotis:
 * Įgyvendinti programą gebančią analizuoti bet kokios .csv
 * rinkmenos turinį. Analizės tikslas nustatyti, automatiniu būdu,
 * kiekvieno analizuojamo stulpelio tipą, pvz., atitinkančius .arff tipus.
 * Konvertuoti galutinį rezultatą į .arff formatą.
 * 
 * Programoje naudojamos išorinės bibliotekos:
 * hsqldb.jar
 * opencsv-2.2.jar
 * 
 * @author justyna.zachazevska@mif.stud.vu.lt
 */

import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.regex.Pattern;

import au.com.bytecode.opencsv.CSVReader;

public class CsvToArffConverter {
	private String dataBaseName = "converter_db";
	private String tableName = "converter_table";
	private String fileName = null;
	private String[] columnNames = null;
	private String[] dataTypes = null;
	private DB db = null;
	private char separator;
	private boolean ignoreFirstLine;
	private int maxNominalValues;

	CsvToArffConverter(String fileName, char separator,
			boolean ignoreFirstLine, int maxNominalValues) {
		this.fileName = fileName;
		this.separator = separator;
		this.ignoreFirstLine = ignoreFirstLine;
		this.maxNominalValues = maxNominalValues;
	}

	public void convert() {
		this.columnNames = getColumnNames();
		prepareDB();
		this.dataTypes = getDataTypes();
		createArffFile();
		shutdownDB();
	}

	/**
	 * Susieja duomenų bazę su cvs rinkmena.
	 * 
	 */

	private void prepareDB() {
		try {
			db = new DB(dataBaseName);
		} catch (Exception ex1) {
			ex1.printStackTrace();
			return;
		}

		StringBuilder createTableStatement = new StringBuilder();
		createTableStatement.append("CREATE TEXT TABLE ").append(tableName)
				.append(" (");
		for (int i = 0; i < columnNames.length; i++) {
			createTableStatement.append(columnNames[i]).append(" ").append(
					"VARCHAR(256)");

			if ((i + 1) < columnNames.length) {
				createTableStatement.append(", ");
			}
		}
		createTableStatement.append(")");

		try {
			db.update(createTableStatement.toString());
			String a = "SET TABLE " + tableName + " SOURCE \"" + fileName
					+ ";all_quoted=true"
					+ (ignoreFirstLine ? ";ignore_first=true" : "") + "\"";
			db.update(a);
		} catch (SQLException ex2) {
			System.err.println("KLAIDA: Panašu, kad stulepelių pavadinimai csv rinkmenoje neatitinka standartinių reikalavimų.");
			ex2.printStackTrace();
		}
	}

	/**
	 * Sustabdo duomenų bazę. Išvalo lentelę.
	 * 
	 */

	public void shutdownDB() {
		try {
			db.update("DROP TABLE " + tableName);
			db.shutdown();
		} catch (SQLException ex3) {
			return;
		}
	}

	/**
	 * Įrašo duomenis į *.arff rinkmeną.
	 * 
	 */

	private void createArffFile() {
		try {
			// Create file
			FileWriter fstream = new FileWriter(fileName.replace(".csv",
					".arff"));
			BufferedWriter out = new BufferedWriter(fstream);
			out.write("@RELATION "
					+ fileName.substring(0, fileName.indexOf(".")) + "\n\n");

			for (int i = 0; i < columnNames.length; i++) {
				out.write("@ATTRIBUTE " + columnNames[i] + "\t"
						+ this.dataTypes[i] + "\n");
			}
			out.write("\n@DATA\n");

			CSVReader reader = new CSVReader(new FileReader(fileName),
					separator, '\"', (ignoreFirstLine ? 1 : 0));
			String[] dataLine;
			while ((dataLine = reader.readNext()) != null) {
				for (int i = 0; i < dataLine.length; i++) {
					if ("".equals(dataLine[i])) {
						out.write("?");
					} else {
						out.write((dataLine[i].contains(" ") ? "\""
								+ dataLine[i] + "\"" : dataLine[i]));
					}
					if (i + 1 < dataLine.length) {
						out.write(",");
					} else {
						out.write("\n");
					}
				}
			}
			out.close();
		} catch (Exception e) {
			System.err.println("Error: " + e.getMessage());
		}
	}

	/**
	 * Nuskaito stulpelių pavadinimus.
	 * 
	 * @return
	 */

	private String[] getColumnNames() {

		CSVReader reader;
		String[] dataLine = null;
		try {
			reader = new CSVReader(new FileReader(fileName), separator);
			dataLine = reader.readNext();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		if (ignoreFirstLine) {
			for (int i = 0; i < dataLine.length; i++) {
				dataLine[i] = dataLine[i].replace(" ", "_");
			}
			return dataLine;
		} else {
			String[] genColumnNames = new String[dataLine.length];
			for (int i = 0; i < genColumnNames.length; i++) {
				genColumnNames[i] = "column_" + (i + 1);
			}
			return genColumnNames;
		}
	}

	/**
	 * Nustato duomenų tipus pagal arff standartą.
	 * 
	 * @return
	 */

	private String[] getDataTypes() {
		String[] dataTypes = null;

		try {
			CSVReader reader = new CSVReader(new FileReader(fileName),
					separator, '\"', 1);

			String[] dataLine = reader.readNext();
			dataTypes = new String[dataLine.length];

			ArrayList<Integer> emptyTypes = new ArrayList<Integer>();
			for (int i = 0; i < dataLine.length; i++) {
				emptyTypes.add(i);
			}

			ArrayList<Integer> removeElements = new ArrayList<Integer>();

			while (emptyTypes.size() > 0) {
				for (int i = 0; i < emptyTypes.size(); i++) {
					if (!"".equals(dataLine[emptyTypes.get(new Integer(i))])) {
						dataTypes[emptyTypes.get(new Integer(i))] = this
								.getArffDataType(dataLine[emptyTypes
										.get(new Integer(i))]);
						removeElements.add(emptyTypes.get(new Integer(i)));
					}
				}

				for (int i = 0; i < removeElements.size(); i++) {
					emptyTypes.remove(removeElements.get(i));
				}
				removeElements.clear();
				dataLine = reader.readNext();
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		// Nominalių reikšmių paieška
		int uniqueCount = 0;
		ArrayList<String> uniqueValues = null;
		for (int i = 0; i < dataTypes.length; i++) {
			if ("string".equals(dataTypes[i])) {
				uniqueCount = getCountOfUniqueStringValues(columnNames[i]);
				if (uniqueCount > 0 && uniqueCount <= maxNominalValues) {

					uniqueValues = getUniqueStrigValues(columnNames[i]);
					if (uniqueValues != null) {
						dataTypes[i] = formClassDataType(uniqueValues);
					}
				}
			}
		}

		return dataTypes;
	}

	/**
	 * Suskirsto duomenų tipus į arff rinkmenoje naudojamus tipus.
	 * 
	 * @param entry
	 * @return
	 */

	private String getArffDataType(String entry) {
		try {
			Double.parseDouble(entry);
			return "numeric";
		} catch (Exception e) {
			if (isDate(entry)) {
				return "date";
			} else {
				return "string";
			}
		}
	}

	/**
	 * Grąžina kiekį unikalių string reikšmių stulpelyje.
	 * 
	 * @param colName
	 * @return
	 */

	private int getCountOfUniqueStringValues(String colName) {
		try {
			ArrayList<String> result = db
					.query("select count(*) from (select distinct " + colName
							+ " from " + tableName + ");");
			return Integer.parseInt(result.get(0));
		} catch (NumberFormatException e) {
			return 0;
		} catch (SQLException e) {
			return 0;
		}
	}

	/**
	 * Grąžina sąrašą unikalių reikšmių stulpelyje.
	 * 
	 * @param colName
	 * @return
	 */

	private ArrayList<String> getUniqueStrigValues(String colName) {
		try {
			ArrayList<String> result = db.query("select distinct " + colName
					+ " from " + tableName);
			return result;
		} catch (SQLException e) {
			return null;
		}
	}

	/**
	 * Suformuoja nominaliojo duomenų tipo aprašymą.
	 * 
	 * @param nominalValues
	 * @return
	 */

	private String formClassDataType(ArrayList<String> nominalValues) {
		StringBuilder builder = new StringBuilder();
		builder.append("{");
		for (int i = 0; i < nominalValues.size(); i++) {
			if (nominalValues.get(i).contains(" ")) {
				builder.append("\"").append(nominalValues.get(i)).append("\"");
			} else {
				builder.append(nominalValues.get(i));
			}
			if (i + 1 < nominalValues.size()) {
				builder.append(",");
			}
		}
		builder.append("}");

		return builder.toString();
	}

	/**
	 * Funkcija, gebanti nustatyti, ar pateiktas stringas yra datos/laiko tipo.
	 * 
	 * @param date
	 * @return
	 */

	public static boolean isDate(CharSequence date) {
		// some regular expression
		String time = "(\\s(([01]?\\d)|(2[0123]))[:](([012345]\\d)|(60))"
				+ "([:](([012345]\\d)|(60))|)([:](([0-9][0-9][0-9])|)))?"; // with
		// a
		// space
		// before,
		// zero
		// or
		// one time

		// no check for leap years (Schaltjahr)
		// and 31.02.2006 will also be correct
		String day = "(([12]\\d)|(3[01])|(0?[1-9]))"; // 01 up to 31
		String month = "((1[012])|(0\\d))"; // 01 up to 12
		String year = "\\d{4}";

		// define here all date format
		ArrayList<Pattern> patterns = new ArrayList<Pattern>();
		patterns.add(Pattern.compile(day + "[-.]" + month + "[-.]" + year
				+ time));
		patterns.add(Pattern.compile(year + "-" + month + "-" + day + time));
		// here you can add more date formats if you want

		// check dates
		for (Pattern p : patterns)
			if (p.matcher(date).matches())
				return true;

		return false;
	}

	public static void usageMessage() {
		System.out
				.println("Usage: CsvToArffConverter.jar <fileName> <seperator> <ignoreFirstLine> <nominalAttributesAmount>");
		System.out
				.println("Example: CsvToArffConverter.jar Test.csv , false 3");
	}

	public static void main(String[] args) {

		if (args.length < 1 && args.length > 4) {
			usageMessage();
		} else {
			CsvToArffConverter converter = null;
			try {
				String fileName = args[0];
				char separator = args[1].toCharArray()[0];
				boolean ignoreFirstLine = Boolean.valueOf(args[2]);
				int nomAttrAmount = Integer.parseInt(args[3]);

				converter = new CsvToArffConverter(fileName, separator,
						ignoreFirstLine, nomAttrAmount);
			} catch (Exception e) {
				usageMessage();
				System.exit(0);
			}

			long start = System.currentTimeMillis();
			converter.convert();
			long end = System.currentTimeMillis();

			System.out.println("Completed: " + (double) (end - start) / 1000
					+ " s.");

		}
	}
}
